IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetConsentFormFooter]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetConsentFormFooter]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/***************************************************************************
* Name:			GetConsentFormFooter
* Purpose:		Get the details required for the Consent form footer
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* @documentId			Document Id to which the footer details has to be added
*
***************************************************************************/
CREATE Procedure [dbo].[GetConsentFormFooter]
(@protocolDocumentId int)

AS BEGIN
SET NOCOUNT ON
Declare @documentName varchar(50)
Declare @protocolNumber varchar(12)
Declare @version varchar(100)
Declare @approvalDate DateTime
Declare @expirationDate Datetime
Declare @amendmentNumber varchar(12)
Declare @protocolId int
Declare @processId int
Declare @processType varchar(256)
Declare @amendmentApprovaldate DateTime
Declare @sponsorAmendmentNumber varchar(100)

SELECT
 	@documentName=pd.[Name],					
	@protocolNumber=p.[ProtocolNumber],				       
	@version=CASE p.[version] when '' THEN 'N/A' ELSE p.[version] END,					       
	@approvalDate=p.[ApprovalDate],				
	@expirationDate=p.[ExpirationDate],				
	@amendmentNumber=pd.[FormsId],
	@protocolId=p.[Id],
	@processId=pp.[Id],
	@processType=pp.[type] 
	FROM Protocol p						
INNER JOIN ProtocolDocument pd				
	ON p.[Id]=pd.[ProtocolId]
LEFT JOIN ProtocolProcess pp
	ON pp.[Id]=pd.[AddedPPId]
Where   pd.[Id]=@protocolDocumentId  


IF (@amendmentNumber like 'AME%' )
BEGIN

  SELECT
	@approvalDate=max(br.[NotificationDate])
  FROM ProtocolSubmission ps
	inner Join ProtocolBoardReview br on ps.[Id] = br.ProtocolsubmissionId
	inner Join IRBBoard b on br.BoardId = b.[Id]
  WHERE
	ps.ProtocolId=@protocolId and
	b.BoardName like '%IRB%' and
	br.BoardStatusId = 1 and
	ps.FormsId = @amendmentNumber

  SELECT
	@amendmentApprovaldate=max(br.[StatusModifiedDate])
  FROM ProtocolSubmission ps
	inner Join ProtocolBoardReview br on ps.[Id] = br.ProtocolsubmissionId
	inner Join IRBBoard b on br.BoardId = b.[Id]
	inner join ProtocolReviewProcessType pvpt on br.ReviewProcessId = pvpt.Id
  WHERE
	ps.ProtocolId=@protocolId and
	b.BoardName like '%IRB%' and
	br.BoardStatusId = 7 and
	ps.FormsId = @amendmentNumber and  
	pvpt.Name like 'Full%'

	IF @amendmentApprovaldate is NULL
	BEGIN

	  SELECT
		@amendmentApprovaldate=max(br.[StatusModifiedDate])
	  FROM ProtocolSubmission ps
		inner Join ProtocolBoardReview br on ps.[Id] = br.ProtocolsubmissionId
		inner Join IRBBoard b on br.BoardId = b.[Id]
		inner join ProtocolReviewProcessType pvpt on br.ReviewProcessId = pvpt.Id
	  WHERE
		ps.ProtocolId=@protocolId and
		b.BoardName like '%IRB%' and
		br.BoardStatusId = 1 and
		ps.FormsId = @amendmentNumber

	END

END
ELSE
BEGIN
  set @amendmentNumber = null

  SELECT
	@approvalDate=max(br.[NotificationDate])
  FROM ProtocolSubmission ps
	 inner Join ProtocolBoardReview br on ps.[Id] = br.ProtocolsubmissionId
	 inner Join IRBBoard b	on br.BoardId = b.[Id]
  WHERE
	ps.ProtocolId=@protocolId and
	b.BoardName like '%IRB%' and
	br.BoardStatusId = 1 and
	( ps.FormsId like 'IR%' or ps.FormsId like 'CR%' )

END


IF ( @processType = 'PPTYPE_AM'  AND  @processId is not null )
BEGIN
		SELECT @sponsorAmendmentNumber=a.value  
			FROM QuestionnaireAnswer qa 
		INNER JOIN Answer a 
			ON qa.[Id]=a.QuestionnaireAnswerId 
		INNER JOIN ProtocolVersion_QuestionnaireAnswer_Map Map
			ON Map.QuestionnaireAnswerId = QA.Id
		WHERE Map.ProtocolVersionId =
			(SELECT MAX(pv.[Id]) FROM ProtocolVersion pv
			WHERE protocolProcessId=@processId and a.[Name]='SponsorAmendNo')
END

SELECT  @documentName as DocumentName,
 @protocolNumber as ProtocolNumebr, 
 @version as Version,
 @approvaldate as ApprovalDate,
 @expirationDate as ExpireDate,
 @amendmentNumber as AmendmentNumber,
 @amendmentApprovaldate as AmendmentApprovaldate,
 @sponsorAmendmentNumber as SponsorAmendmentNumber	

END
GO
